use serde::Serialize;
use sqlx::{Executor, SqliteConnection};
use sqlx::Row;
use crate::error::{new_err_static, AppErr, SqlxErr};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_dev_type(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE
    )
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

#[derive(Debug, Serialize)]
pub struct DevType {
    id: i64,
    name: String,
}

async fn is_exists(tx: &mut SqliteConnection, name: &str) -> Result<bool, SqlxErr> {
    let ret = sqlx::query(
        r#"
    SELECT id FROM tb_dev_type WHERE name = ? LIMIT 1
    "#,
    )
    .bind(name)
    .fetch_optional(tx)
    .await?
    .is_some();
    Ok(ret)
}

pub async fn create(name: &str) -> Result<DevType, AppErr> {
    let mut tx = get_pool().begin().await?;

    if is_exists(&mut tx, name).await? {
        return Err(new_err_static("类型已经存在"));
    }

    let row = sqlx::query(
        r#"
        INSERT INTO tb_dev_type (name) VALUES (?)
    "#,
    )
    .bind(name)
    .execute(&mut *tx)
    .await?;

    let id = row.last_insert_rowid();

    let val = DevType {
        id,
        name: name.to_owned(),
    };

    tx.commit().await?;

    Ok(val)
}


pub async fn delete(id: i64) -> Result<(), AppErr> {

    sqlx::query(r#"
        DELETE FROM tb_dev_type WHERE id = ?
    "#)
    .bind(id)
    .execute( get_pool() )
    .await?;
    Ok(())
}

pub async fn query() -> Result<Box<[DevType]>, AppErr> {

    let rows = sqlx::query(r#"
        SELECT id, name FROM tb_dev_type 
    "#)
    .fetch_all( get_pool() )
    .await?;

    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let v = DevType {
            id: row.get(1),
            name: row.get(1)
        };
        vec.push(v);
    }
    Ok(vec.into_boxed_slice())
}
